package entity

import (
	"fmt"
	"time"

	"gorm.io/gorm"
	"shqsoft.cn/pas3ui/base"
)

//Shidnicliedr 零售关联关系导入
type Shidnicliedr struct {
	ID   int    `gorm:"AUTO_INCREMENT;primary_key;column:id"`
	IDNO string `gorm:"column:idno"`
	CUNM string `gorm:"column:cunm"`
	CUID int    `gorm:"column:cuid"`
}

//TableName
func (Shidnicliedr) TableName() string {
	return "SH_INDI_CLIE_DR"
}

// BeforeCreate
func (u *Shidnicliedr) BeforeCreate(tx *gorm.DB) (err error) {
	base.CHDB.Raw("select ID,CUNM from T_INDI_CLIE where IDNO=?", u.IDNO).Row().Scan(&u.CUID, &u.CUNM)
	if u.CUID == 0 {
		err = fmt.Errorf("证件号码%s在系统中不存在！", u.IDNO)
		return
	}
	return
}

//ShCompClieZcldEF 返回结构体
func ShidnicliedrEF() base.EntityFunc {
	return func() interface{} {
		entity := Shidnicliedr{}
		return &entity
	}
}

//ShCompClieZcldESF 返回结构体数组
func ShidnicliedrESF() base.EntitiesFunc {
	return func() interface{} {
		var entities []Shidnicliedr
		return &entities
	}
}
func ShidnicliedrAfterImport(tx *gorm.DB, params map[string]string) (err error) {
	tx.Commit()
	var date *time.Time
	date, _, err = base.GetSYSDate("EDW")
	if err != nil {
		return
	}
	err = CreateIndiYgfc(date.Format("2006-01-02"))
	return
}

//CreateIndiYgfc 构造零售客户认定关系
func CreateIndiYgfc(currDate string) (err error) {
	strSql := "CREATE TABLE IF NOT EXISTS SH_INDI_CLIE_DR_DIVI(CUID UInt64,`DIVI.DPID` Array(UInt32),`DIVI.EMID` Array(UInt32),`DIVI.DSVL` Array(Float64)) ENGINE = Join(ANY,LEFT,CUID)"
	err = base.CHDB.Exec(strSql).Error
	if err != nil {
		return
	}
	err = base.CHDB.Exec("TRUNCATE TABLE SH_INDI_CLIE_DR_DIVI").Error
	if err != nil {
		return
	}
	strSql = "CREATE TABLE IF NOT EXISTS SH_INDI_CLIE_DR_DIVI2(CUID UInt64,`DIVI.DPID` Array(UInt32),`DIVI.EMID` Array(UInt32),`DIVI.DSVL` Array(Float64)) ENGINE = Join(ANY,LEFT,CUID)"
	err = base.CHDB.Exec(strSql).Error
	if err != nil {
		return
	}
	err = base.CHDB.Exec("TRUNCATE TABLE SH_INDI_CLIE_DR_DIVI2").Error
	if err != nil {
		return
	}
	where := "AND CUID IN (SELECT cuid FROM mysql.SH_INDI_CLIE_DR)"
	strSql = fmt.Sprintf(`INSERT INTO SH_INDI_CLIE_DR_DIVI(CUID,DIVI.DPID,DIVI.EMID,DIVI.DSVL)
	SELECT A.CUID,groupArray(B.DPID) DPIDS,groupArray(B.EMID) EMIDS,groupArray(B.ACBL/A.ACBL) DSVLS FROM
	(SELECT CUID,SUM(ACBL) ACBL FROM
	(SELECT CUID,SUM(ACBL1) ACBL FROM DF_INDI_ACCO_ACBL WHERE DTID=toDate(?) %s AND ACBL1>0 GROUP BY CUID
	UNION ALL
	SELECT CUID,SUM(ACBL1) ACBL FROM DF_INDI_FANA_ACBL WHERE DTID=toDate(?) %s AND ACBL1>0 GROUP BY CUID)
	GROUP BY CUID) A
	INNER JOIN 
	(SELECT CUID,DPID,EMID,SUM(ACBL) ACBL FROM
	(SELECT CUID,DPID,EMID,SUM(ACBL1) ACBL FROM DF_INDI_ACCO_ACBL WHERE DTID=toDate(?) %s AND ACBL1>0 GROUP BY CUID,DPID,EMID
	UNION ALL
	SELECT CUID,DPID,EMID,SUM(ACBL1) ACBL FROM DF_INDI_FANA_ACBL WHERE DTID=toDate(?) %s AND ACBL1>0 GROUP BY CUID,DPID,EMID)
	GROUP BY CUID,DPID,EMID
	) B ON A.CUID=B.CUID
	GROUP BY A.CUID`, where, where, where, where)
	err = base.CHDB.Exec(strSql, currDate, currDate, currDate, currDate).Error
	if err != nil {
		return
	}
	strSql = fmt.Sprintf(`INSERT INTO SH_INDI_CLIE_DR_DIVI2(CUID,DIVI.DPID,DIVI.EMID,DIVI.DSVL)
	SELECT B.CUID,DIVI.DPID DPIDS,DIVI.EMID EMIDS,DIVI.DSVL DSVLS
	FROM T_INDI_ACCO_DIVI_ZIPPER A INNER JOIN 
	(SELECT CUID,MAX(ID) ACID FROM T_INDI_ACCO WHERE CUID NOT IN (SELECT CUID FROM SH_INDI_CLIE_DR_DIVI) %s GROUP BY CUID) B ON A.ACID=B.ACID
	WHERE toDate(?)>=A.SDATE AND toDate(?)<A.EDATE`, where)
	err = base.CHDB.Exec(strSql, currDate, currDate).Error
	if err != nil {
		return
	}
	err = base.CHDB.Exec("INSERT INTO SH_INDI_CLIE_DR_DIVI SELECT * FROM SH_INDI_CLIE_DR_DIVI2").Error
	return
}

func init() {
	base.DaoPoolObj.Put("Shidnicliedr", base.NewDefaultDao(ShidnicliedrEF(), ShidnicliedrESF(), nil, "my"))
	base.SetAfterCallBack("Shidnicliedr", ShidnicliedrAfterImport)
}
